package com.cwj.poi;

import com.itextpdf.text.*;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import okhttp3.Headers;
import okhttp3.Response;
import org.apache.commons.codec.binary.StringUtils;
import org.apache.poi.POIXMLTextExtractor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.extractor.XSSFEventBasedExcelExtractor;
import org.apache.poi.xssf.extractor.XSSFExcelExtractor;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.XmlException;
import org.openjdk.jol.info.ClassLayout;
import org.openjdk.jol.info.FieldData;
import org.openjdk.jol.info.FieldLayout;
import org.openjdk.jol.info.GraphLayout;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet;
import org.xml.sax.*;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import javax.swing.plaf.TableHeaderUI;
import java.io.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

/**
 * @author wujie.chen
 * 2021/9/17 16:17
 */
public class LoadExcelFile {
    static int i;
    Object sd = 'c';
    Pattern rowCompile = Pattern.compile(".*\\r\\n");
    Pattern phraseCompile = Pattern.compile(".*?(?=\\t|\\r\\n|$)");
    public static void main(String[] args)
        throws OpenXML4JException, IOException, XmlException, DocumentException, InterruptedException, SAXException {
        Thread.sleep(20000);
        String fileName = "src/main/resources/test.xlsx";
        String s = readTextByBase(fileName);
        System.out.println(s);
        //        write2pdf(s);
//        System.out.println(i);
//        test01();
//        test02("src/main/resources/人员20211013.xlsx");
//        test02("src/main/resources/test.xlsx");
        Thread.sleep(20000);
    }


    public static void write2pdf(String text) throws DocumentException, IOException {
        Document document = new Document();
        document.open();

        PdfPTable table = new PdfPTable(100);
        table.setWidthPercentage(100);

        PdfPCell pdfpCell = new PdfPCell();
//        BaseFont font = BaseFont.createFont("STSongStd-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED);
//        Font result = new Font(font , 8 , Font.NORMAL);
        Phrase phrase = new Phrase("你好");
        pdfpCell.setPhrase(phrase);

        table.addCell(pdfpCell);
        table.setKeepTogether(true);
        table.getDefaultCell().setBorder(PdfPCell.NO_BORDER);
        document.add(table);
        document.close();

        FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\wujie.chen\\IdeaProjects\\java1.8\\src\\main\\resources\\task.pdf");
        PdfWriter writer = PdfWriter.getInstance(document, fileOutputStream);
        writer.close();

    }

    public static String readText(String path) throws XmlException, OpenXML4JException, IOException {
        OPCPackage open = OPCPackage.open(path);
        return readText(open);
    }
    public static String readText(InputStream inputStream) throws XmlException, OpenXML4JException, IOException {
        OPCPackage open = OPCPackage.open(inputStream);
        return readText(open);
    }
    public static String readText(OPCPackage container) throws XmlException, OpenXML4JException, IOException {
        POIXMLTextExtractor poixmlTextExtractor = new XSSFEventBasedExcelExtractor(container);
        return poixmlTextExtractor.getText();
    }

    public static String readTextByBase(String path) throws OpenXML4JException, XmlException, IOException {
        OPCPackage open = OPCPackage.open(path);
        XSSFExcelExtractor xssfExcelExtractor = new XSSFExcelExtractor(open);
        return xssfExcelExtractor.getText();

    }

    /**
     * 把文件输入流写入到文件系统中的缓存文件
     * @param inputStream 文件流
     * @param fileName  源文件名
     * @return  临时文件路径
     * @throws FileNotFoundException
     */
    private String temp(InputStream inputStream, String fileName) throws FileNotFoundException {
        String tmpPath = System.getProperty("java.io.tmpdir");
        long currentTimeMillis = System.currentTimeMillis();
        String newFileName = fileName.replaceFirst(".+(?=\\.pdf)", Long.toString(currentTimeMillis));
        String newFilePath = tmpPath + "/" + newFileName;

        try (OutputStream outputStream = new FileOutputStream(newFilePath)) {
            byte[] data = new byte[1024];
            int length;
            while((length = inputStream.read(data)) > 0){
                outputStream.write(data, 0, length);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("newFilePath: " + newFilePath);
        return newFilePath;

    }

    /**
     * SXSSF 通过限制对滑动窗口内的行的访问来实现其低内存占用，而 XSSF 允许访问文档中的所有行。
     * 不再出现在窗口中的旧行将无法访问，因为它们被写入磁盘。
     * @throws IOException
     * @throws InterruptedException
     */
    public static void test01() throws IOException, InterruptedException {
        Thread.sleep(20000);
        long l1 = System.currentTimeMillis();
        SXSSFWorkbook wb = new SXSSFWorkbook(-1);
        Sheet sheet = wb.createSheet();
        for (int i = 0; i < 100000; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                String s = new CellReference(cell).formatAsString();
                cell.setCellValue(s);
            }
            if(i % 100 == 0){
                ((SXSSFSheet)sheet).flushRows(100);
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream("src/main/resources/test.xlsx");
        wb.write(fileOutputStream);
        fileOutputStream.close();
        wb.dispose();
        long l2 = System.currentTimeMillis();
        System.out.println(l2 - l1);
    }


    public static void test02(String fileName) throws OpenXML4JException, IOException, SAXException {
        OPCPackage open = OPCPackage.open(fileName);
        XSSFReader reader = new XSSFReader(open);
        SharedStringsTable sst = reader.getSharedStringsTable();
        DefaultHandler defaultHandler = new SheetHandler(sst);
        XMLReader xmlReader = fetchSheetParser(sst, defaultHandler);

        Iterator<InputStream> sheetsData = reader.getSheetsData();
        while (sheetsData.hasNext()){
            InputStream sheet1 = sheetsData.next();
            InputSource inputSource = new InputSource(sheet1);
            xmlReader.parse(inputSource);
            sheet1.close();
        }

    }
    private static XMLReader fetchSheetParser(SharedStringsTable sst, DefaultHandler handler) throws SAXException {
        XMLReader xmlReader = XMLReaderFactory.createXMLReader();

        xmlReader.setContentHandler(handler);
        return xmlReader;
    }

    private static class SheetHandler extends DefaultHandler{
        private SharedStringsTable sst;
        private boolean nextIsString;
        private String lastContents;

        public SheetHandler(SharedStringsTable sst) {
            this.sst = sst;
        }

        @Override
        public void startElement(String uri, String localName, String qName, Attributes attributes)
            throws SAXException {
            if(qName.equals("c")){
                System.out.print(attributes.getValue("r") + " - ");
                String cellType = attributes.getValue("t");
                if(cellType!=null && cellType.equals("s")){
                    nextIsString = true;
                }else{
                    nextIsString = false;
                }
            }
            lastContents = "";
        }

        @Override
        public void characters(char[] ch, int start, int length) throws SAXException {
            lastContents += new String(ch, start, length);
        }

        @Override
        public void endElement(String uri, String localName, String qName) throws SAXException {
            if(nextIsString){
                int idx = Integer.parseInt(lastContents);
                lastContents = sst.getItems().get(idx).getT();
                nextIsString = false;
            }

            if(qName.equals("v")){
                System.out.println(lastContents);
            }
        }
    }

    public static void readLargeSheet(){
        try (InputStream sourceBytes = new FileInputStream("workbook.xlsx")) {
            XSSFWorkbook workbook = new XSSFWorkbook(sourceBytes);

            // Having avoided a DOM-based parse of the sheet, we can stream it instead.
            ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(workbook.getPackage());
            new XSSFSheetXMLHandler(workbook.getStylesSource(), strings, createSheetContentsHandler(), false);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException | SAXException e) {
            e.printStackTrace();
        }
    }
    private static List<String[]> data = new ArrayList();
    private static XSSFSheetXMLHandler.SheetContentsHandler createSheetContentsHandler() {
        return new XSSFSheetXMLHandler.SheetContentsHandler() {

            @Override
            public void startRow(int rowNum) {
                data.add(rowNum, new String[5]);
            }

            @Override
            public void endRow() {

            }

            @Override
            public void cell(String cellReference, String formattedValue) {

            }

            @Override
            public void headerFooter(String text, boolean isHeader, String tagName) {

            }
        };
    }
    private static final String SHEET_TO_STREAM = "sheet0";



}
